/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Rick
 */
class mySQLConnectionDAO implements ConnectionDAO {

    PreparedStatement ps = null;
    ResultSet rs = null;
    Connection conn = null;

    public mySQLConnectionDAO() {
            conn = SQLConfigeration.getConnection();
    }

    /**
     * Used to insert into the connection table
     *
     * @param Server
     * @param ipaddress
     * @param username
     * @param city
     * @param region
     * @param country
     * @return the autoId of the tuple inserted
     */
    public int addConnection(String Server, String ipaddress,
                    String username, String city,
                    String region, String country) {
        int id = 0;
        
        try {
            String insertConn = "insert into Connection(username, city," +
                    " region, country, ipaddress, server) values (?, ?, ?, ?, ?, ?)";
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(insertConn);
            ps.setString(1, username);
            ps.setString(2, city);
            ps.setString(3, region);
            ps.setString(4, country);
            ps.setString(5, ipaddress);
            ps.setString(6, Server);
            System.out.println("Attempting insert");
            ps.executeUpdate();
            conn.commit();
            System.out.println("Connection Insert Successful");

            id = findConnectionAutoID(username);

        } catch (SQLException ex) {
            System.out.println("Insert Error: " + ex);
        }finally{
            try {
                ps.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }

         return id;
    }

    /**
     * deletes a user from the table
     * not currently being used
     *
     * @param username
     * @return boolean deletion success
     */
    public boolean deleteConnection(String username) {
        boolean deleteSuccess = false;
        String deleteConnection = "delete from Connection where filename = '" + username + "'";

        try{
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(deleteConnection);

            System.out.println("Attempting Deletion");
            ps.executeUpdate();
            conn.commit();
            System.out.println("Delection Successful");
            deleteSuccess = true;

        }catch(SQLException ex){
            System.out.println("Deletion Error: " + ex);
        }
        return deleteSuccess;
    }


    /**
     * An early attempt to gether queries
     * not currently being used
     *
     * @param username
     * @return an array of Connection Objects
     */
    public ConnectionStore[] findConnectionUserName(String username) {
        ConnectionStore[] connectionInfo = new ConnectionStore[50];
        String query = "Select autoid, username, city, region, country, ipaddress" +
                " from Connection where username = ?";
        try{
            ps = conn.prepareStatement(query);
            ps.setString(1, username);
            rs = ps.executeQuery();

            rs.next();

            System.out.println("Attempting to find connection");
            int index = 0;
            while(rs.next()){
                connectionInfo[index].setAutoID(rs.getInt("autoid"));
                connectionInfo[index].setUserName(rs.getString("username"));
                connectionInfo[index].setCity(rs.getString("city"));
                connectionInfo[index].setRegion(rs.getString("region"));
                connectionInfo[index].setCountry(rs.getString("country"));
                connectionInfo[index].setIpaddress(rs.getString("ipaddress"));
                index++;
            }
        }catch(SQLException ex){
            System.out.println("UserName Find Error: " + ex);
        }finally{
            try {
                rs.close();
                ps.close();
                //conn.close();
            } catch (SQLException ex) {
                System.out.println("SQL Error: " + ex);
            }
        }

        return connectionInfo;
    }


    /**
     * used to determine if user exists in table
     *
     * @param username
     * @return -1 if user does not exist autoID if user exists
     */
    public int findConnectionAutoID(String username) {
        int index = 0;
        String query = "Select autoid from Connection where username = ?";
        try{
            ps = conn.prepareStatement(query);
            ps.setString(1, username);
            rs = ps.executeQuery();

            //System.out.println("Attempting to see if user exists");
            while(!rs.isLast()){
                rs.next();
                index = rs.getInt("autoid");
                //System.out.println("User found at Index: " + index);
            }
            System.out.println("Last User inserted at Index: " + index);

        }catch(SQLException ex){
            index = -1;
            System.out.println("User not Found");
        }finally{
            try {
                rs.close();
                ps.close();
                //conn.close();
            } catch (SQLException ex) {
                System.out.println("SQL Error: " + ex);
            }
        }
        return index;
    }

    public boolean updateConnection(String username) {
        throw new UnsupportedOperationException("Not supported yet.");
    }
}
